﻿<%@ WebHandler Language="C#" Class="RbAEOSItemMaint" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Collections;
using ReportBusiness;

/*
 * Description: 热表厂AEOS指标维护
 * Copyright (c) : 通力凯顿（北京）系统集成有限公司
 * Writer:zhunan
 * Date:2023.5.23
 */

public class RbAEOSItemMaint : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getData":
                        r = getData(jObject);//查询数据
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "checkIsExist":
                        r = checkIsExist(jObject);//判断是否存在
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "insertData":
                        r = insertData(jObject);//新增
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "changeStatus":
                        r = changeStatus(jObject);//切换启用状态
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "deleteData":
                        r = deleteData(jObject);//删除数据
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "updateData":
                        r = updateData(jObject);//更新数据
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "insertDetailData":
                        r = insertDetailData(jObject);//插入指标每日数据
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getItemDetailsData":
                        r = getItemDetailsData(jObject);//查询指标每日数据
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "updateItemDetailsData":
                        r = updateItemDetailsData(jObject);//更新每日执行情况
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "deleteItemDetail":
                        r = deleteItemDetail(jObject);//每日执行情况删除
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    //查询问题类型
    Result getData(JObject jObject)
    {
        string sql = @"
SELECT t.*,f.factoryname,w.workcentername,te.teamname,e.fullname creater,e2.fullname updater,
       CASE WHEN te.teamname IS NOT NULL THEN f.factoryname || '/' || w.workcentername || '/' || te.teamname
            WHEN te.teamname IS NULL AND w.workcentername IS NOT NULL THEN f.factoryname || '/' || w.workcentername
            ELSE f.factoryname
       END orginfo,
       CASE WHEN t.itemname IN ('一次交检合格率','在制品批次数','计划按时完成率') THEN '1' ELSE '0' END disabled
FROM rbaeositem t
LEFT JOIN factory f ON f.factoryid = t.factoryid
LEFT JOIN workcenter w ON w.workcenterid = t.workcenterid
LEFT JOIN team te ON te.teamid = t.teamid
LEFT JOIN employee e ON e.employeeid = t.createemployee
LEFT JOIN employee e2 ON e2.employeeid = t.lastchangeemployee
WHERE 1=1 ";

        //年月
        if (jObject["yearmonth"] != null & jObject["yearmonth"].ToString() != "")
        {
            sql += string.Format(" AND t.yearmonth = '{0}'", jObject["yearmonth"].ToString());
        }

        //类型
        if (jObject["sqcdptype"] != null & jObject["sqcdptype"].ToString() != "")
        {
            sql += string.Format(" AND t.sqcdptype = '{0}'", jObject["sqcdptype"].ToString());
        }

        //指标名称
        if (jObject["itemname"] != null & jObject["itemname"].ToString() != "")
        {
            sql += string.Format(" AND t.itemname LIKE '%{0}%'", jObject["itemname"].ToString());
        }

        //组织结构
        if (jObject["selectOrg"] != null && jObject["selectOrg"].ToString() != "[]")
        {
            JArray selectOrg = JArray.Parse(jObject["selectOrg"].ToString());
            if (selectOrg.Count == 3)
            {
                sql += string.Format(" AND t.factoryid = '{0}'", selectOrg[0].ToString());
                sql += string.Format(" AND t.workcenterid = '{0}'", selectOrg[1].ToString());
                sql += string.Format(" AND t.teamid = '{0}'", selectOrg[2].ToString());
            }
            else if (selectOrg.Count == 2)
            {
                sql += string.Format(" AND t.factoryid = '{0}'", selectOrg[0].ToString());
                sql += string.Format(" AND t.workcenterid = '{0}'", selectOrg[1].ToString());
            }
            else
            {
                sql += string.Format(" AND t.factoryid = '{0}'", selectOrg[0].ToString());
            }
        }

        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //判断是否存在
    Result checkIsExist(JObject jObject)
    {
        string factoryid = "", workcenterid = "", teamid = "";
        JArray selectOrg = JArray.Parse(jObject["selectOrg"].ToString());
        if (selectOrg.Count == 3)
        {
            factoryid = selectOrg[0].ToString();
            workcenterid = selectOrg[1].ToString();
            teamid = selectOrg[2].ToString();
        }
        else if (selectOrg.Count == 2)
        {
            factoryid = selectOrg[0].ToString();
            workcenterid = selectOrg[1].ToString();
        }
        else
        {
            factoryid = selectOrg[0].ToString();
        }

        string sql = @"
SELECT * FROM rbaeositem t 
WHERE t.yearmonth = '{0}' AND t.sqcdptype='{1}' AND t.itemname='{2}'  
      AND t.factoryid='{3}' AND NVL(t.workcenterid,'0')='{4}' AND NVL(t.teamid,'0')='{5}'";

        if (jObject["id"] != null && jObject["id"].ToString() != "")
        {
            sql += string.Format(" AND t.id <> '{0}'", jObject["id"].ToString());
        }

        sql = string.Format(sql,
                            jObject["yearmonth"].ToString(),
                            jObject["sqcdptype"].ToString(),
                            jObject["itemname"].ToString(),
                            factoryid != "" ? factoryid : "0",
                            workcenterid != "" ? workcenterid : "0",
                            teamid != "" ? teamid : "0"
                            );

        DataTable dt = dal.GetDataTable(sql);
        Result r = new Result();
        r.data = null;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //新增数据
    Result insertData(JObject jObject)
    {
        string factoryid = "", workcenterid = "", teamid = "", orgtype = "";
        JArray selectOrg = JArray.Parse(jObject["selectOrg"].ToString());
        if (selectOrg.Count == 3)
        {
            factoryid = selectOrg[0].ToString();
            workcenterid = selectOrg[1].ToString();
            teamid = selectOrg[2].ToString();
            orgtype = "班组级";
        }
        else if (selectOrg.Count == 2)
        {
            factoryid = selectOrg[0].ToString();
            workcenterid = selectOrg[1].ToString();
            orgtype = "工区级";
        }
        else
        {
            factoryid = selectOrg[0].ToString();
            orgtype = "分厂级";
        }

        string sql = @"
INSERT INTO rbaeositem(id,yearmonth,sqcdptype,itemname,itemnotes,factoryid,workcenterid,teamid,orgtype,unit,threshold,createemployee,createdate,determine)
VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}',sysdate,'{12}')";

        sql = string.Format(sql,
                            Guid.NewGuid().ToString(),//唯一id
                            jObject["yearmonth"].ToString(),//年月
                            jObject["sqcdptype"].ToString(),//sqcpd类型
                            jObject["itemname"].ToString(),//指标名称
                            jObject["itemnotes"].ToString(),//指标描述
                            factoryid,//分厂
                            workcenterid,//工区
                            teamid,//班组
                            orgtype,//指标颗粒度
                            jObject["unit"].ToString(),//单位
                            jObject["threshold"].ToString(),//阈值
                            jObject["employeeid"].ToString(),//创建人
                            jObject["determine"].ToString() //超值判断
                            );
        int count = dal.ExecuteSql(sql);
        Result r = new Result();
        r.data = null;
        r.total = Convert.ToInt32(count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //切换启用状态
    Result changeStatus(JObject jObject)
    {
        string sql = @"UPDATE rbaeositem t SET t.status='{1}',t.lastchangedate = sysdate,t.lastchangeemployee='{2}' WHERE t.id='{0}' ";
        int status = 0;
        if (jObject["status"] != null)
        {
            status = jObject["status"].ToString() == "True" ? 1 : 0;
        }

        sql = string.Format(sql, jObject["id"].ToString(), status, jObject["employeeid"].ToString());
        int count = dal.ExecuteSql(sql);
        Result r = new Result();
        r.data = null;
        r.total = count;
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //删除数据
    Result deleteData(JObject jObject)
    {
        ArrayList sqlList = new ArrayList();
        sqlList.Add(string.Format("DELETE rbaeositem t WHERE t.id='{0}'", jObject["id"].ToString()));
        sqlList.Add(string.Format("DELETE rbaeositemdetail t WHERE t.rbaeositemid='{0}'", jObject["id"].ToString()));
        dal.ExecuteSqlTran(sqlList);
        Result r = new Result();
        r.data = null;
        r.total = 1;
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //更新数据
    Result updateData(JObject jObject)
    {
        string factoryid = "", workcenterid = "", teamid = "", orgtype = "";
        JArray selectOrg = JArray.Parse(jObject["selectOrg"].ToString());
        if (selectOrg.Count == 3)
        {
            factoryid = selectOrg[0].ToString();
            workcenterid = selectOrg[1].ToString();
            teamid = selectOrg[2].ToString();
            orgtype = "班组级";
        }
        else if (selectOrg.Count == 2)
        {
            factoryid = selectOrg[0].ToString();
            workcenterid = selectOrg[1].ToString();
            orgtype = "工区级";
        }
        else
        {
            factoryid = selectOrg[0].ToString();
            orgtype = "分厂级";
        }
        string sql = @"
UPDATE rbaeositem t 
SET t.yearmonth = '{1}',t.sqcdptype = '{2}',t.itemname = '{3}',t.itemnotes = '{4}',
    t.factoryid = '{5}',t.workcenterid = '{6}',t.teamid = '{7}',t.orgtype = '{8}',
    t.unit = '{9}',t.threshold = '{10}',t.lastchangeemployee = '{11}',t.lastchangedate = sysdate,
    t.determine = '{12}'
WHERE t.id = '{0}'";

        sql = string.Format(sql,
                            jObject["id"].ToString(),//唯一id
                            jObject["yearmonth"].ToString(),//年月
                            jObject["sqcdptype"].ToString(),//sqcpd类型
                            jObject["itemname"].ToString(),//指标名称
                            jObject["itemnotes"].ToString(),//指标描述
                            factoryid,//分厂
                            workcenterid,//工区
                            teamid,//班组
                            orgtype,//指标颗粒度
                            jObject["unit"].ToString(),//单位
                            jObject["threshold"].ToString(),//阈值
                            jObject["employeeid"].ToString(),//修改人
                            jObject["determine"].ToString()//超值判断
                            );
        int count = dal.ExecuteSql(sql);
        Result r = new Result();
        r.data = null;
        r.total = Convert.ToInt32(count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //插入指标每日数据
    Result insertDetailData(JObject jObject)
    {
        string containerid = "", workflowstepid = "";
        string strContainerInfo = "SELECT cci.containerid,cci.workflowstepid FROM containercurrentinfo cci WHERE cci.containername = '{0}'";
        strContainerInfo = string.Format(strContainerInfo, jObject["containername"].ToString());
        DataTable dtContainer = dal.GetDataTable(strContainerInfo);
        if (dtContainer.Rows.Count > 0)
        {
            containerid = dtContainer.Rows[0]["containerid"].ToString();
            workflowstepid = dtContainer.Rows[0]["workflowstepid"].ToString();
        }

        string sql = @"
INSERT INTO rbaeositemdetail(id,rbaeositemid,description,improvemeasures,needfinishdate,finishdate,schedule,dealemployee,createemployee,createdate,containerid,workflowstepid)
VALUES('{0}','{1}','{2}','{3}',to_date('{4}','yyyy/mm/dd'),to_date('{5}','yyyy/mm/dd'),'{6}','{7}','{8}',to_date('{9}','yyyy/mm/dd'),'{10}','{11}')";

        sql = string.Format(sql,
                            Guid.NewGuid().ToString(),//唯一id
                            jObject["id"].ToString(),//父表id
                            jObject["description"].ToString(),//问题描述
                            jObject["improvemeasures"].ToString(),//整改措施
                            jObject["needfinishdate"].ToString(),//要求整改时间
                            jObject["finishdate"].ToString(),//完成时间
                            jObject["schedule"].ToString(),//当前进度
                            jObject["dealemployee"].ToString(),//责任人
                            jObject["createemployee"].ToString(),//创建人
                            jObject["createdate"].ToString(),//创建时间
                            containerid,
                            workflowstepid
                            );
        int count = dal.ExecuteSql(sql);
        Result r = new Result();
        r.data = null;
        r.total = Convert.ToInt32(count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //查询指标每日数据明细
    Result getItemDetailsData(JObject jObject)
    {
        string sql = @"
SELECT te.id,te.rbaeositemid,t.yearmonth,t.sqcdptype,t.itemname,t.itemnotes,t.orgtype,t.unit,t.threshold,t.determine,t.status,
       CASE WHEN ta.teamname IS NOT NULL THEN f.factoryname || '/' || w.workcentername || '/' || ta.teamname
            WHEN ta.teamname IS NULL AND w.workcentername IS NOT NULL THEN f.factoryname || '/' || w.workcentername
            ELSE f.factoryname
       END orginfo,
       CASE WHEN t.itemname IN ('一次交检合格率','在制品批次数','计划按时完成率') THEN '1' ELSE '0' END disabled,
       te.description,te.improvemeasures,te.needfinishdate,te.finishdate,te.schedule,te.dealemployee,cci.containername,ws.workflowstepname,
       te.createdate,e.fullname creater,te.lastchangedate,e2.fullname updater
FROM rbaeositemdetail te
LEFT JOIN rbaeositem t ON t.id = te.rbaeositemid
LEFT JOIN factory f ON f.factoryid = t.factoryid
LEFT JOIN workcenter w ON w.workcenterid = t.workcenterid
LEFT JOIN team ta ON ta.teamid = t.teamid
LEFT JOIN employee e ON e.employeeid = te.createemployee
LEFT JOIN employee e2 ON e2.employeeid = te.lastchangeemployee
LEFT JOIN containercurrentinfo cci ON cci.containerid = te.containerid
LEFT JOIN workflowstep ws ON ws.workflowstepid = te.workflowstepid
WHERE t.id = '{0}'
ORDER BY te.createdate";
        sql = string.Format(sql, jObject["id"].ToString());
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //更新每日执行情况
    Result updateItemDetailsData(JObject jObject)
    {
        string containerid = "", workflowstepid = "";
        string strContainerInfo = "SELECT cci.containerid,cci.workflowstepid FROM containercurrentinfo cci WHERE cci.containername = '{0}'";
        strContainerInfo = string.Format(strContainerInfo, jObject["containername"].ToString());
        DataTable dtContainer = dal.GetDataTable(strContainerInfo);
        if (dtContainer.Rows.Count > 0)
        {
            containerid = dtContainer.Rows[0]["containerid"].ToString();
            workflowstepid = dtContainer.Rows[0]["workflowstepid"].ToString();
        }

        string sql = @"
UPDATE rbaeositemdetail t 
SET t.description = '{1}',t.improvemeasures = '{2}',t.needfinishdate = to_date('{3}','yyyy/mm/dd'),
    t.finishdate = to_date('{4}','yyyy/mm/dd'),t.schedule = '{5}',t.dealemployee = '{6}',t.containerid = '{7}',t.workflowstepid = '{8}',
    t.lastchangeemployee = '{9}' ,t.lastchangedate = sysdate
WHERE t.id = '{0}'";

        sql = string.Format(sql,
                            jObject["id"].ToString(),//唯一id
                            jObject["description"].ToString(),//问题描述
                            jObject["improvemeasures"].ToString(),//整改措施
                            jObject["needfinishdate"].ToString() == "" ? "" : jObject["needfinishdate"].ToString().Split(' ')[0],//要求整改时间
                            jObject["finishdate"].ToString() == "" ? "" : jObject["finishdate"].ToString().Split(' ')[0],//完成时间
                            jObject["schedule"].ToString(),//当前进度
                            jObject["dealemployee"].ToString(),//责任人
                            containerid,
                            workflowstepid,
                            jObject["employeeid"].ToString()//修改人
                            );
        int count = dal.ExecuteSql(sql);
        Result r = new Result();
        r.data = null;
        r.total = Convert.ToInt32(count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //指标每日执行情况--删除
    Result deleteItemDetail(JObject jObject)
    {
        ArrayList sqlList = new ArrayList();
        sqlList.Add(string.Format("DELETE rbaeositemdetail t WHERE t.id='{0}'", jObject["id"].ToString()));
        dal.ExecuteSqlTran(sqlList);
        Result r = new Result();
        r.data = null;
        r.total = 1;
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }
}